import sys
from PyQt5 import *
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *
from ui_MainWindow import *
from PyQt5.QtSql import *
from myDelegates import QmyComboBoxDelegate
import qdarkstyle
class QmyWindow(QMainWindow):
    def __init__(self,parent=None):
        super().__init__(parent)
        self.ui=Ui_MainWindow()
        self.ui.setupUi(self)
        self.setCentralWidget(self.ui.splitter)
        #显示属性设置
        self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectItems)
        self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
        self.ui.tableView.setAlternatingRowColors(True)
        self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
        self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
    #获取字段的函数
    def __getFieldNames(self):
        emptyRec=self.tabModel.record()
        self.fldNum={}
        for i in range(emptyRec.count()):
            fieldName=emptyRec.fieldName(i)
            self.ui.comboFields.addItem(fieldName)
            self.fldNum.setdefault(fieldName)
            self.fldNum[fieldName]=i

    def __openTable(self):
        #数据模型
        self.tabModel=QSqlTableModel(self,self.DB)
        #设置数据表
        self.tabModel.setTable("employee")
        #数据的保存方式
        self.tabModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
        #排序
        self.tabModel.setSort(self.tabModel.fieldIndex("empNo"),Qt.AscendingOrder)
        #查询数据
        if (self.tabModel.select()==False): #查询失败
            QMessageBox.critical(self,"错误信息","打开数据库表错误,错误信息\n"+self.tabModel.lastError().text())
            return

        #获取字段名
        self.__getFieldNames()#获取字段名和序号
        #字段显示名
        self.tabModel.setHeaderData(self.fldNum["empNo"],Qt.Horizontal,"工号")
        self.tabModel.setHeaderData(self.fldNum["Name"], Qt.Horizontal, "姓名")
        self.tabModel.setHeaderData(self.fldNum["Gender"], Qt.Horizontal, "性别")
        self.tabModel.setHeaderData(self.fldNum["Birthday"], Qt.Horizontal, "出生日期")
        self.tabModel.setHeaderData(self.fldNum["Province"], Qt.Horizontal, "省份")
        self.tabModel.setHeaderData(self.fldNum["Department"], Qt.Horizontal, "部门")
        self.tabModel.setHeaderData(self.fldNum["Salary"], Qt.Horizontal, "工资")
        self.tabModel.setHeaderData(self.fldNum["Memo"], Qt.Horizontal, "备注")
        self.tabModel.setHeaderData(self.fldNum["Photo"], Qt.Horizontal, "照片")

        #创建界面组件与数据模型之间的映射
        self.mapper=QDataWidgetMapper()
        self.mapper.setModel(self.tabModel)#设置数据模型
        self.mapper.setSubmitPolicy(QDataWidgetMapper.AutoSubmit)


        #设置界面组件与tabmodel之间具体字段的联系
        self.mapper.addMapping(self.ui.dbSpinEmpNo,self.fldNum["empNo"])
        self.mapper.addMapping(self.ui.dbEditName, self.fldNum["Name"])
        self.mapper.addMapping(self.ui.dbComboSex, self.fldNum["Gender"])
        self.mapper.addMapping(self.ui.dbEditBirth,self.fldNum["Birthday"])
        self.mapper.addMapping(self.ui.dbComboProvince, self.fldNum["Province"])
        self.mapper.addMapping(self.ui.dbComboDep, self.fldNum["Department"])
        self.mapper.addMapping(self.ui.dbSpinSalary,self.fldNum["Salary"])
        self.mapper.addMapping(self.ui.dbEditMemo, self.fldNum["Memo"])

        #移动到首记录
        self.mapper.toFirst()
        #选择模型
        self.selModel=QItemSelectionModel(self.tabModel)#选择模型
        self.selModel.currentChanged.connect(self.do_currentChanged)#当前选项变化是触发
        self.selModel.currentRowChanged.connect(self.do_currentRowChanged)#选择行发生变化时
        #设置数据类型
        self.ui.tableView.setModel(self.tabModel)
        #设置选择模型
        self.ui.tableView.setSelectionModel(self.selModel)
        #隐藏的列
        self.ui.tableView.setColumnHidden(self.fldNum["Memo"],True)

        self.ui.tableView.setColumnHidden(self.fldNum["Photo"],True)
        strList=("男","女")
        self.__delegateSex=QmyComboBoxDelegate()
        self.__delegateSex.setItems(strList,False)
        self.ui.tableView.setItemDelegateForColumn(self.fldNum["Gender"],self.__delegateSex)
        strList = ("销售部", "技术部","生产部","行政部")
        self.__delegateDepart=QmyComboBoxDelegate()
        self.__delegateDepart.setItems(strList,True)
        self.ui.tableView.setItemDelegateForColumn(self.fldNum["Department"],self.__delegateDepart)
        self.ui.actOpenDB.setEnabled(False)
        self.ui.actRecAppend.setEnabled(True)
        self.ui.actRecInsert.setEnabled(True)
        self.ui.actRecDelete.setEnabled(True)
        self.ui.actScan.setEnabled(True)
        self.ui.groupBoxSort.setEnabled(True)
        self.ui.groupBoxFilter.setEnabled(True)

    #打开数据库
    @pyqtSlot()
    def on_actOpenDB_triggered(self):
        dbFilename='./demodb.db3'
        if (dbFilename==''):
            return
        #打开数据库

        self.DB=QSqlDatabase.addDatabase("QSQLITE")
        self.DB.setDatabaseName(dbFilename)
        if self.DB.open():

            self.__openTable() #打开数据表

        else:
            QMessageBox.warning(self,"错误","打开数据库失败")

    def do_currentChanged(self,current,previous):
        self.ui.actSubmit.setEnabled(self.tabModel.isDirty())
        self.ui.actRevert.setEnabled(self.tabModel.isDirty())
    def do_currentRowChanged(self,current,previous):
        self.ui.actRecDelete.setEnabled(current.isValid())
        self.ui.actPhoto.setEnabled(current.isValid())
        self.ui.actPhotoClear.setEnabled(current.isValid())
        if(current.isValid()==False):
            #清除图片
            self.ui.dbLabPhoto.clear()
            return
        self.mapper.setCurrentIndex(current.row())#更新数据映射行号
        curRec=self.tabModel.record(current.row())#获取当前的记录
        if (curRec.isNull("Photo")):
            self.ui.dbLabPhoto.clear()
        else:
            data=curRec.value("Photo")
            pic=QPixmap()
            pic.loadFromData(data)
            w=self.ui.dbLabPhoto.size().width()
            self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(w))
    @pyqtSlot()
    def on_actRecAppend_triggered(self):
        self.tabModel.insertRow(self.tabModel.rowCount(),QModelIndex())#在末尾添加一个记录
        #最后一行创建一个model index
        cueIndex=self.tabModel.index(self.tabModel.rowCount()-1,1)
        #清空选择项
        self.selModel.clearSelection()
        #设置刚插入的行为当前的行
        self.selModel.setCurrentIndex(cueIndex,QItemSelectionModel.Select)
        currow=cueIndex.row()#获得当前行
        self.tabModel.setData(self.tabModel.index(currow,self.fldNum["empNo"]),2000+self.tabModel.rowCount())
        self.tabModel.setData(self.tabModel.index(currow,self.fldNum["Gender"]),"男")
    @pyqtSlot()
    def on_actSubmit_triggered(self):
        res=self.tabModel.submitAll()
        if(res==False):
            QMessageBox.information(self,"消息","数据保存错误",self.tabModel.lastError().text())
        else:
            self.ui.actSubmit.setEnabled(False)
            self.ui.actRevert.setEnabled(False)
    #取消修改
    @pyqtSlot()
    def on_actRevert_triggered(self):
        self.tabModel.revertAll()
        self.ui.actSubmit.setEnabled(False)
        self.ui.actRevert.setEnabled(False)
    #插入记录
    @pyqtSlot()
    def on_actRecInsert_triggered(self):
        curIndex=self.ui.tableView.currentIndex()
        self.tabModel.insertRow(curIndex.row(),QModelIndex())
        self.selModel.clearSelection()
        self.selModel.setCurrentIndex(curIndex,QItemSelectionModel.Select)

    @pyqtSlot()
    def on_actRecDelete_triggered(self):
        curIndex=self.selModel.currentIndex()
        self.tabModel.removeRow(curIndex.row())

    @pyqtSlot()
    def on_actPhotoClear_triggered(self):
        curRecNo=self.selModel.currentIndex().row()
        curRec=self.tabModel.record(curRecNo)#获取记录
        curRec.setNull("Photo")
        self.tabModel.setRecord(curRecNo,curRec)
        self.ui.dbLabPhoto.clear()


    @pyqtSlot()
    def on_actPhoto_triggered(self):
        fileName,filt=QFileDialog.getOpenFileName(self,"选择图片","","照片(*.jpg)")
        if (fileName==''):
            return
        file=QFile(fileName)
        file.open(QIODevice.ReadOnly)
        try:
            data=file.readAll()
        finally:
            file.close()
        curRecNo=self.selModel.currentIndex().row()
        curRec=self.tabModel.record(curRecNo)
        curRec.setValue("Photo",data)
        self.tabModel.setRecord(curRecNo,curRec)
        pic=QPixmap()
        pic.loadFromData(data)
        w=self.ui.dbLabPhoto.width()
        self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(w))
    @pyqtSlot()
    def on_actScan_triggered(self):
        if (self.tabModel.rowCount()==0):
            return
        for i in range(self.tabModel.rowCount()):
            arec=self.tabModel.record(i)
            salary=arec.value("Salary")
            salary=salary*1.1
            arec.setValue("Salary",salary)
            self.tabModel.setRecord(i,arec)


if __name__ == '__main__':
    app=QApplication(sys.argv)
    app.setStyleSheet(qdarkstyle.load_stylesheet_pyqt5())
    mywin=QmyWindow()
    mywin.show()
    sys.exit(app.exec_())